using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Collections.Generic;

/// <summary>
/// Summary description for ProductBll
/// </summary>
public class ProductBll
{
    private const string SQL_SELECT_PRODUCTS = "SELECT * FROM PRODUCTS ORDER BY ID DESC";
    private const string SQL_SELECT_PRODUCT_BYID = "SELECT * FROM PRODUCTS WHERE ID = @ID";
    private const string SQL_SELECT_PRODUCTS_NEW = "SELECT top 9 * FROM PRODUCTS ORDER BY ID DESC";
    private const string SQL_SELECT_PRODUCTS_BY_HANGSANXUAT = "SELECT * FROM PRODUCTS WHERE HANGSANXUAT LIKE @HANGSANXUAT";
    private const string SQL_SELECT_PRODUCTS_BY_SOGHE = "SELECT * FROM PRODUCTS WHERE SOGHE = @SOGHE";
    private const string SQL_DELETE_PRODUCTS = "DELETE FROM PRODUCTS WHERE ID = @ID";
    private const string SQL_INSERT_PRODUCTS = "INSERT INTO PRODUCTS(HANGSANXUAT, XUATXU, MODEL, TINHTRANG, HOPSO, CHUNGLOAIXE, NAM, KICHTHUOC, CHIEUDAI, TUTRONG, TONGTRONGLUONG, XILANH, CONGSUAT, MOMEN, NHIENLIEU, TIEUHAONL, DUNGTICHNL, SOCUA, SOGHE, COLOPTRUOC, COLOPSAU, IMAGE1, IMAGE2, IMAGE3, IMAGE4, IMAGE5, TRANGTHIETBI) VALUES (@HANGSANXUAT, @XUATXU, @MODEL, @TINHTRANG, @HOPSO, @CHUNGLOAIXE, @NAM, @KICHTHUOC, @CHIEUDAI, @TUTRONG, @TONGTRONGLUONG, @XILANH, @CONGSUAT, @MOMEN, @NHIENLIEU, @TIEUHAONL, @DUNGTICHNL, @SOCUA, @SOGHE, @COLOPTRUOC, @COLOPSAU, @IMAGE1, @IMAGE2, @IMAGE3, @IMAGE4, @IMAGE5, @TRANGTHIETBI)";
    private const string SQL_UPDATE_PRODUCTS = "UPDATE PRODUCTS SET HANGSANXUAT = @HANGSANXUAT, XUATXU = @XUATXU, MODEL = @MODEL, TINHTRANG = @TINHTRANG, HOPSO = @HOPSO, CHUNGLOAIXE = @CHUNGLOAIXE, NAM = @NAM, KICHTHUOC = @KICHTHUOC, CHIEUDAI = @CHIEUDAI, TUTRONG = @TUTRONG, TONGTRONGLUONG = @TONGTRONGLUONG, XILANH = @XILANH, CONGSUAT= @CONGSUAT, MOMEN = @MOMEN, NHIENLIEU = @NHIENLIEU, TIEUHAONL = @TIEUHAONL, DUNGTICHNL = @DUNGTICHNL, SOCUA = @SOCUA, SOGHE = @SOGHE, COLOPTRUOC = @COLOPTRUOC, COLOPSAU = @COLOPSAU, IMAGE1 = @IMAGE1, IMAGE2 = @IMAGE2, IMAGE3 = @IMAGE3, IMAGE4 = @IMAGE4, IMAGE5 = @IMAGE5, TRANGTHIETBI = @TRANGTHIETBI WHERE ID = @ID";
    private const string PARM_ID = "@ID";
    private const string PARM_HANGSANXUAT = "@HANGSANXUAT";
    private const string PARM_XUATXU = "@XUATXU";
    private const string PARM_MODEL = "@MODEL";
    private const string PARM_TINHTRANG = "@TINHTRANG";
    private const string PARM_HOPSO = "@HOPSO";
    private const string PARM_CHUNGLOAIXE = "@CHUNGLOAIXE";
    private const string PARM_NAM = "@NAM";
    private const string PARM_KICHTHUOC = "@KICHTHUOC";
    private const string PARM_CHIEUDAI = "@CHIEUDAI";
    private const string PARM_TUTRONG = "@TUTRONG";
    private const string PARM_TONGTRONGLUONG = "@TONGTRONGLUONG";
    private const string PARM_XILANH = "@XILANH";
    private const string PARM_CONGSUAT = "@CONGSUAT";
    private const string PARM_MOMEN = "@MOMEN";
    private const string PARM_NHIENLIEU = "@NHIENLIEU";
    private const string PARM_TIEUHAONL = "@TIEUHAONL";
    private const string PARM_DUNGTICHNL = "@DUNGTICHNL";
    private const string PARM_SOCUA = "@SOCUA";
    private const string PARM_SOGHE = "@SOGHE";
    private const string PARM_COLOPTRUOC = "@COLOPTRUOC";
    private const string PARM_COLOPSAU = "@COLOPSAU";
    private const string PARM_IMAGE1 = "@IMAGE1";
    private const string PARM_IMAGE2 = "@IMAGE2";
    private const string PARM_IMAGE3 = "@IMAGE3";
    private const string PARM_IMAGE4 = "@IMAGE4";
    private const string PARM_IMAGE5 = "@IMAGE5";
    private const string PARM_TRANGTHIETBI = "@TRANGTHIETBI";

	public ProductBll()
	{
    }
	
    private Product ProductManipulate(SqlDataReader reader)
    {
        Product p = new Product();
        if (!reader.IsDBNull(0))
            p.Id = reader.GetInt32(0);
        if (!reader.IsDBNull(1))
            p.HangSanXuat = reader.GetString(1);
        if (!reader.IsDBNull(2))
            p.XuatXu = reader.GetString(2);
        if (!reader.IsDBNull(3))
            p.Model = reader.GetString(3);
        if (!reader.IsDBNull(4))
            p.TinhTrang = reader.GetString(4);
        if (!reader.IsDBNull(5))
            p.HopSo = reader.GetString(5);
        if (!reader.IsDBNull(6))
            p.ChungLoaiXe = reader.GetString(6);
        if (!reader.IsDBNull(7))
            p.Nam = reader.GetInt32(7);
        if (!reader.IsDBNull(8))
            p.KichThuoc = reader.GetString(8);
        if (!reader.IsDBNull(9))
            p.ChieuDai = reader.GetString(9);
        if (!reader.IsDBNull(10))
            p.TuTrong = reader.GetString(10);
        if (!reader.IsDBNull(11))
            p.TongTrongLuong = reader.GetString(11);
        if (!reader.IsDBNull(12))
            p.Xilanh = reader.GetString(12);
        if (!reader.IsDBNull(13))
            p.CongSuat = reader.GetString(13);
        if (!reader.IsDBNull(14))
            p.Momen = reader.GetString(14);
        if (!reader.IsDBNull(15))
            p.NhienLieu = reader.GetString(15);
        if (!reader.IsDBNull(16))
            p.TieuHaoNL = reader.GetString(16);
        if (!reader.IsDBNull(17))
            p.DungTichNL = reader.GetInt32(17);
        if (!reader.IsDBNull(18))
            p.SoCua = reader.GetInt32(18);
        if (!reader.IsDBNull(19))
            p.SoGhe = reader.GetInt32(19);
        if (!reader.IsDBNull(20))
            p.CoLopTruoc = reader.GetString(20);
        if (!reader.IsDBNull(21))
            p.CoLopSau = reader.GetString(21);
        if (!reader.IsDBNull(22))
            p.Image1 = reader.GetString(22);
        if (!reader.IsDBNull(23))
            p.Image2 = reader.GetString(23);
        if (!reader.IsDBNull(24))
            p.Image3 = reader.GetString(24);
        if (!reader.IsDBNull(25))
            p.Image4 = reader.GetString(25);
        if (!reader.IsDBNull(26))
            p.Image5 = reader.GetString(26);
        if (!reader.IsDBNull(27))
            p.TrangThietBi = reader.GetString(27);
        return p;
    }

    public IList<Product> Get9NEW()
    {
        IList<Product> result = new List<Product>();
        using (SqlDataReader reader = SQLDBHelper.ExecuteReader(SQLDBHelper.CONN_STRING,
            CommandType.Text, SQL_SELECT_PRODUCTS_NEW, null))
        {
            while (reader.Read())
            {
                Product p = ProductManipulate(reader);
                result.Add(p);
            }
        }
        return result;
    }

    public IList<Product> GetAll()
    {
        IList<Product> result = new List<Product>();
        using (SqlDataReader reader = SQLDBHelper.ExecuteReader(SQLDBHelper.CONN_STRING,
            CommandType.Text, SQL_SELECT_PRODUCTS, null))
        {
            while (reader.Read())
            {
                Product p = ProductManipulate(reader);
                result.Add(p);
            }
        }
        return result;
    }
    public Product GetByID(int id)
    {
        SqlParameter[] parms;
        parms = new SqlParameter[] { new SqlParameter(PARM_ID, SqlDbType.Int) };
        parms[0].Value = id;

        using (SqlDataReader reader = SQLDBHelper.ExecuteReader(SQLDBHelper.CONN_STRING,
            CommandType.Text, SQL_SELECT_PRODUCT_BYID, parms))
        {
            if (reader.Read())
            {
                Product p = ProductManipulate(reader);
                return p;
            }
        }
        return null;
    }

    public IList<Product> GetByHangSanXuat(string hangSanXuat)
    {
        SqlParameter[] parms;
        parms = new SqlParameter[] { new SqlParameter(PARM_HANGSANXUAT, SqlDbType.NVarChar) };
        parms[0].Value = hangSanXuat;

        IList<Product> result = new List<Product>();

        using (SqlDataReader reader = SQLDBHelper.ExecuteReader(SQLDBHelper.CONN_STRING,
            CommandType.Text, SQL_SELECT_PRODUCTS_BY_HANGSANXUAT, parms))
        {
            while (reader.Read())
            {
                Product p = ProductManipulate(reader);
                result.Add(p);
            }
        }
        return result;
    }

    public IList<Product> GetBySoGhe(int soGhe)
    {
        SqlParameter[] parms;
        parms = new SqlParameter[] { new SqlParameter(PARM_SOGHE, SqlDbType.Int) };
        parms[0].Value = soGhe;

        IList<Product> result = new List<Product>();

        using (SqlDataReader reader = SQLDBHelper.ExecuteReader(SQLDBHelper.CONN_STRING,
            CommandType.Text, SQL_SELECT_PRODUCTS_BY_SOGHE, parms))
        {
            while (reader.Read())
            {
                Product p = ProductManipulate(reader);
                result.Add(p);
            }
        }
        return result;
    }

    public bool Delete(int id)
    {
        SqlParameter[] parms;
        parms = new SqlParameter[] { new SqlParameter(PARM_ID, SqlDbType.Int) };
        parms[0].Value = id;

        try
        {
            int result = 0;
            result = SQLDBHelper.ExecuteNonQuery(SQLDBHelper.CONN_STRING,
                CommandType.Text, SQL_DELETE_PRODUCTS, parms);
            return (result > 0 ? true : false);
        }
        catch (Exception e)
        {
            throw e;
        }
        return false;
    }

    public bool Update(Product p)
    {
        SqlParameter[] parms;
        parms = new SqlParameter[] {
            new SqlParameter(PARM_ID, SqlDbType.Int),
            new SqlParameter(PARM_HANGSANXUAT, SqlDbType.NVarChar),
            new SqlParameter(PARM_XUATXU, SqlDbType.NVarChar),
            new SqlParameter(PARM_MODEL, SqlDbType.NVarChar),
            new SqlParameter(PARM_TINHTRANG, SqlDbType.NVarChar),
            new SqlParameter(PARM_HOPSO, SqlDbType.NVarChar),
            new SqlParameter(PARM_CHUNGLOAIXE, SqlDbType.NVarChar),
            new SqlParameter(PARM_NAM, SqlDbType.Int),
            new SqlParameter(PARM_KICHTHUOC, SqlDbType.NVarChar),
            new SqlParameter(PARM_CHIEUDAI, SqlDbType.NVarChar),
            new SqlParameter(PARM_TUTRONG, SqlDbType.NVarChar),
            new SqlParameter(PARM_TONGTRONGLUONG, SqlDbType.NVarChar),
            new SqlParameter(PARM_XILANH, SqlDbType.NVarChar),
            new SqlParameter(PARM_CONGSUAT, SqlDbType.NVarChar),
            new SqlParameter(PARM_MOMEN, SqlDbType.NVarChar),
            new SqlParameter(PARM_NHIENLIEU, SqlDbType.NVarChar),
            new SqlParameter(PARM_TIEUHAONL, SqlDbType.NVarChar),
            new SqlParameter(PARM_DUNGTICHNL, SqlDbType.Int),
            new SqlParameter(PARM_SOCUA, SqlDbType.Int),
            new SqlParameter(PARM_SOGHE, SqlDbType.Int),
            new SqlParameter(PARM_COLOPTRUOC, SqlDbType.VarChar),
            new SqlParameter(PARM_COLOPSAU, SqlDbType.VarChar),
            new SqlParameter(PARM_IMAGE1, SqlDbType.VarChar),
            new SqlParameter(PARM_IMAGE2, SqlDbType.VarChar),
            new SqlParameter(PARM_IMAGE3, SqlDbType.VarChar),
            new SqlParameter(PARM_IMAGE4, SqlDbType.VarChar),
            new SqlParameter(PARM_IMAGE5, SqlDbType.VarChar),
            new SqlParameter(PARM_TRANGTHIETBI, SqlDbType.NText)};

        parms[0].Value = p.Id;
        parms[1].Value = p.HangSanXuat;
        parms[2].Value = p.XuatXu;
        parms[3].Value = p.Model;
        parms[4].Value = p.TinhTrang;
        parms[5].Value = p.HopSo;
        parms[6].Value = p.ChungLoaiXe;
        parms[7].Value = p.Nam;
        parms[8].Value = p.KichThuoc;
        parms[9].Value = p.ChieuDai;
        parms[10].Value = p.TuTrong;
        parms[11].Value = p.TongTrongLuong;
        parms[12].Value = p.Xilanh;
        parms[13].Value = p.CongSuat;
        parms[14].Value = p.Momen;
        parms[15].Value = p.NhienLieu;
        parms[16].Value = p.TieuHaoNL;
        parms[17].Value = p.DungTichNL;
        parms[18].Value = p.SoCua;
        parms[19].Value = p.SoGhe;
        parms[20].Value = p.CoLopTruoc;
        parms[21].Value = p.CoLopSau;
        parms[22].Value = p.Image1;
        parms[23].Value = p.Image2;
        parms[24].Value = p.Image3;
        parms[25].Value = p.Image4;
        parms[26].Value = p.Image5;
        parms[27].Value = p.TrangThietBi;

        try
        {
            int result = 0;
            result = SQLDBHelper.ExecuteNonQuery(SQLDBHelper.CONN_STRING,
                CommandType.Text, SQL_UPDATE_PRODUCTS, parms);
            return (result > 0 ? true : false);
        }
        catch (Exception e)
        {
            throw e;
        }
        return false;
    }

    public bool Insert(Product p)
    {
        SqlParameter[] parms;
        parms = new SqlParameter[] {
            new SqlParameter(PARM_HANGSANXUAT, SqlDbType.NVarChar),
            new SqlParameter(PARM_XUATXU, SqlDbType.NVarChar),
            new SqlParameter(PARM_MODEL, SqlDbType.NVarChar),
            new SqlParameter(PARM_TINHTRANG, SqlDbType.NVarChar),
            new SqlParameter(PARM_HOPSO, SqlDbType.NVarChar),
            new SqlParameter(PARM_CHUNGLOAIXE, SqlDbType.NVarChar),
            new SqlParameter(PARM_NAM, SqlDbType.Int),
            new SqlParameter(PARM_KICHTHUOC, SqlDbType.NVarChar),
            new SqlParameter(PARM_CHIEUDAI, SqlDbType.NVarChar),
            new SqlParameter(PARM_TUTRONG, SqlDbType.NVarChar),
            new SqlParameter(PARM_TONGTRONGLUONG, SqlDbType.NVarChar),
            new SqlParameter(PARM_XILANH, SqlDbType.NVarChar),
            new SqlParameter(PARM_CONGSUAT, SqlDbType.NVarChar),
            new SqlParameter(PARM_MOMEN, SqlDbType.NVarChar),
            new SqlParameter(PARM_NHIENLIEU, SqlDbType.NVarChar),
            new SqlParameter(PARM_TIEUHAONL, SqlDbType.NVarChar),
            new SqlParameter(PARM_DUNGTICHNL, SqlDbType.Int),
            new SqlParameter(PARM_SOCUA, SqlDbType.Int),
            new SqlParameter(PARM_SOGHE, SqlDbType.Int),
            new SqlParameter(PARM_COLOPTRUOC, SqlDbType.VarChar),
            new SqlParameter(PARM_COLOPSAU, SqlDbType.VarChar),
            new SqlParameter(PARM_IMAGE1, SqlDbType.VarChar),
            new SqlParameter(PARM_IMAGE2, SqlDbType.VarChar),
            new SqlParameter(PARM_IMAGE3, SqlDbType.VarChar),
            new SqlParameter(PARM_IMAGE4, SqlDbType.VarChar),
            new SqlParameter(PARM_IMAGE5, SqlDbType.VarChar),
            new SqlParameter(PARM_TRANGTHIETBI, SqlDbType.NText)};

        parms[0].Value = p.HangSanXuat;
        parms[1].Value = p.XuatXu;
        parms[2].Value = p.Model;
        parms[3].Value = p.TinhTrang;
        parms[4].Value = p.HopSo;
        parms[5].Value = p.ChungLoaiXe;
        parms[6].Value = p.Nam;
        parms[7].Value = p.KichThuoc;
        parms[8].Value = p.ChieuDai;
        parms[9].Value = p.TuTrong;
        parms[10].Value = p.TongTrongLuong;
        parms[11].Value = p.Xilanh;
        parms[12].Value = p.CongSuat;
        parms[13].Value = p.Momen;
        parms[14].Value = p.NhienLieu;
        parms[15].Value = p.TieuHaoNL;
        parms[16].Value = p.DungTichNL;
        parms[17].Value = p.SoCua;
        parms[18].Value = p.SoGhe;
        parms[19].Value = p.CoLopTruoc;
        parms[20].Value = p.CoLopSau;
        parms[21].Value = p.Image1;
        parms[22].Value = p.Image2;
        parms[23].Value = p.Image3;
        parms[24].Value = p.Image4;
        parms[25].Value = p.Image5;
        parms[26].Value = p.TrangThietBi;

        try
        {
            int result = 0;
            SqlConnection conn = new SqlConnection(SQLDBHelper.CONN_STRING);
            result = SQLDBHelper.ExecuteNonQuery(conn,
                CommandType.Text, SQL_INSERT_PRODUCTS, parms);
            if (conn.State == ConnectionState.Open)
                conn.Close();
            return (result > 0 ? true : false);
        }
        catch (Exception e)
        {
            throw e;
        }
        return false;
    }
}
